package cn.waqwb;

import java.sql.*;
import java.util.ArrayList;

public class connDB {

    private static Connection conn = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;

    public static void startConn() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.134.132:3306/supermarket", "root", "123456");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 该方法用于从数据库中查询用户日志表，按一级分类分组统计数量，并将满足条件的数据存储在列表中返回。
     *
     * @return 一个包含字符串数组的 ArrayList，每个字符串数组包含一级分类和对应的数量
     * @throws SQLException 如果在数据库操作过程中出现 SQL 相关的异常
     */
    public static ArrayList<String[]> index() throws SQLException {
        // 创建一个用于存储结果的 ArrayList，其中每个元素是一个字符串数组
        ArrayList<String[]> list = new ArrayList<String[]>();

        // 调用 startConn 方法建立数据库连接
        startConn();

        // 使用已建立的数据库连接创建一个 Statement 对象，用于执行 SQL 语句
        stmt = conn.createStatement();

        // 执行 SQL 查询语句，从 user_log 表中按 category_I 字段分组，并统计每组的数量
        // 将统计结果的列命名为 num
        rs = stmt.executeQuery("select category_I,count(*) num from user_log group by category_I");

        // 遍历结果集，处理每一行数据
        while (rs.next()) {
            // 从当前行中获取 category_I 和 num 字段的值，并存储在一个字符串数组中
            String[] tmp = {rs.getString("category_I"), rs.getString("num")};

            // 检查一级分类的长度是否大于 2
            if (tmp[0].length() > 2) {
                // 如果长度大于 2，则打印一级分类和对应的数量
                System.out.println(tmp[0] + ":" + tmp[1]);
                // 将满足条件的字符串数组添加到结果列表中
                list.add(tmp);
            }
        }

        // 调用 endConn 方法关闭数据库连接、Statement 对象和结果集
        endConn();

        // 返回存储结果的列表
        return list;
    }

    //category_II of DRINK
    public static ArrayList index_1() throws SQLException {
        ArrayList<String[]> list = new ArrayList();
        startConn();
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select category_II,count(*) num from user_log where category_I='DRINK' group by category_II");
        while (rs.next()) {
            String[] temp = {rs.getString("category_II"), rs.getString("num")};
            if (temp[0].length() > 2) {
                System.out.println(temp[0] + " : " + temp[1]);
                list.add(temp);
            }
        }
        endConn();
        return list;
    }

    public static ArrayList index_2() throws SQLException {
        ArrayList<String[]> list = new ArrayList();
        startConn();
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select distinct city, count(distinct ficheno) num,latitude, longitude from user_log group by city,latitude,longitude");
        while (rs.next()) {
            String[] temp = {rs.getString("city"), rs.getString("num"), rs.getString("latitude"), rs.getString("longitude")};
            if (temp[1].length() > 1) {
                System.out.println(temp[0] + " : " + temp[1] + " -- " + temp[2] + " -- " + temp[3]);
                list.add(temp);
            }
        }
        endConn();
        return list;
    }

    //
    public static ArrayList index_3() throws SQLException {
        ArrayList<String[]> list = new ArrayList();
        startConn();
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select sex,category_I,count(*) num from user_log group by sex,category_I");
        while (rs.next()) {
            String[] temp = {rs.getString("sex"), rs.getString("category_I"), rs.getString("num")};
            System.out.println(temp[0] + " : " + temp[1] + " -- " + temp[2]);
            list.add(temp);
        }
        endConn();
        return list;
    }


    public static ArrayList index_4() throws SQLException {
        ArrayList<String[]> list = new ArrayList();
        startConn();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(" select antecedent,consequent,confidence from rules group by antecedent,consequent,confidence");
        while (rs.next()) {
            String[] temp = {rs.getString("antecedent"), rs.getString("consequent"), rs.getString("confidence")};
            System.out.println(temp[0] + " : " + temp[1] + " -- " + temp[2]);
            list.add(temp);
        }
        endConn();
        return list;
    }

    public static ArrayList index_5() throws SQLException {
        ArrayList<String[]> list = new ArrayList();
        startConn();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(" select distinct itemcode, itemname from user_log, rules where itemcode=antecedent or itemcode =consequent");
        while (rs.next()) {
            String[] temp = {rs.getString("itemcode"), rs.getString("itemname")};
            System.out.println(temp[0] + " : " + temp[1]);
            list.add(temp);
        }
        endConn();
        return list;
    }

    private static void endConn() throws SQLException {
        if (conn != null) {
            conn.close();
            conn = null;
        }
        if (rs != null) {
            rs.close();
            rs = null;
        }
        if (stmt != null) {
            stmt.close();
            stmt = null;
        }
    }
}
